package helloworld.example.customer.dao.msaccess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.ArrayList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import helloworld.example.customer.Address;
import helloworld.example.customer.CustomerDetailObject;
import helloworld.example.customer.list.CustomerSummaryObject;
import helloworld.example.customer.dao.DAOException;
import helloworld.example.customer.dao.CustomerDAO;
import helloworld.example.customer.dao.DuplicateCustomerException;
import helloworld.example.exception.ErrorLevel;
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;

/**
 * CustomerDAO encapsulates data access and manipulation It represents the Data
 * Access Object (DAO) Pattern. A DAO abstract the mechanisms of accessing and
 * manipulating a persistence store such as database,
 * 
 * using SQLParameter-like processor
 * 
 * @author Srikanth Shenoy Tuyen-DM hehehe
 * @version $Revision: $ $Date: 2009/03/13 $
 */
public class CustomerDAO_AccessImpl implements CustomerDAO {

	protected static String DB_FIELDS = "EMAIL, FIRSTNAME, LASTNAME, PREFERRED_CARRIER, CAN_RECV_MAIL, ADDRESS1, ADDRESS2, CITY, STATE, ZIPCODE";
	protected static String VALUES = "(?,?,?,?,?,?,?,?,?,?)";
	protected static String SELECT_SQL = "SELECT ID, " + DB_FIELDS
			+ " FROM CUSTOMER WHERE EMAIL = ?";
	protected static String SEARCH_SQL = "SELECT ID, " + DB_FIELDS
			+ " FROM CUSTOMER WHERE LASTNAME = ? ORDER BY ID";
	protected static String INSERT_SQL = "INSERT INTO CUSTOMER(" + DB_FIELDS
			+ ")" + " VALUES" + VALUES;
	protected static String UPDATE_SQL = "UPDATE CUSTOMER SET EMAIL = ?, FIRSTNAME = ?, LASTNAME = ?, PREFERRED_CARRIER = ?, "
			+ " CAN_RECV_MAIL = ?, ADDRESS1 = ?, ADDRESS2 = ?, CITY = ?, STATE = ?, ZIPCODE = ?"
			+ " WHERE ID = ?";
	protected static String DELETE_SQL = "DELETE FROM CUSTOMER WHERE EMAIL = ?";
	protected static String DELETE_ID_SQL = "DELETE FROM CUSTOMER WHERE ID = ?";

	/**
	 * Constructor
	 */
	public CustomerDAO_AccessImpl() {

	}

	/**
	 * fetch data results to object list
	 * 
	 * @param emailAddress
	 * @return
	 * @throws DAOException
	 */
	public CustomerDetailObject fetchDetail(String emailAddress)
			throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();
		CustomerDetailObject customer = null;
		try {
			pSmnt = conn.prepareStatement(SELECT_SQL);
			pSmnt.setString(1, emailAddress);// set parameter 1

			ResultSet set = pSmnt.executeQuery();
			if (set.next()) {
				customer = extractCustomerDetailObjectFromResultSet(set);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { emailAddress };
			throw new DAOException(se, "error.database.fetch.customerdetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return customer;
	}

	/**
	 * 
	 * @author Tuyen-DM
	 * @param lastName
	 * @return
	 * @throws DAOException
	 */
	public CustomerSummaryObject[] find(String lastName) throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();
		CustomerSummaryObject customer = null;
		List list = new ArrayList();

		try {
			pSmnt = conn.prepareStatement(SEARCH_SQL);
			pSmnt.setString(1, lastName);// set parameter 1

			ResultSet set = pSmnt.executeQuery();
			while (set.next()) {
				customer = extractCustomerSummaryObjectFromResultSet(set);
				list.add(customer);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { lastName };
			throw new DAOException(se, "error.database.fetch.customerdetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		CustomerSummaryObject[] customers = null;
		if (list.size() > 0) {
			customers = (CustomerSummaryObject[]) list
					.toArray(new CustomerSummaryObject[list.size()]);
		}

		return customers;
	}

	/**
	 * insert new customer into database
	 * 
	 * @param customer
	 * @return
	 * @throws DAOException
	 * @throws DuplicateCustomerException
	 */
	public int create(CustomerDetailObject customer) throws DAOException,
			DuplicateCustomerException {
		int insertedRows = 0;
		PreparedStatement pStmt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			CustomerDetailObject det = fetchDetail(customer.getEmailAddress());
			if (det != null) {
				insertedRows = -1;
				throw new DuplicateCustomerException(
						"error.database.customer.duplicate", customer
								.getEmailAddress(), null, ErrorLevel.WARNING);
			} else {

				pStmt = conn.prepareStatement(INSERT_SQL);
				pStmt.setString(1, customer.getEmailAddress());
				pStmt.setString(2, customer.getFirstName());
				pStmt.setString(3, customer.getLastName());
				pStmt.setString(4, customer.getPreferredCarrier());
				pStmt.setBoolean(5, customer.getReceiveEmail());
				pStmt.setString(6, customer.getAddress().getAddress1());
				pStmt.setString(7, customer.getAddress().getAddress2());
				pStmt.setString(8, customer.getAddress().getCity());
				pStmt.setString(9, customer.getAddress().getState());
				pStmt.setString(10, customer.getAddress().getZip());

				insertedRows = pStmt.executeUpdate();
			}

		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { customer.getEmailAddress() };
			throw new DAOException(se, "error.database.customer.save",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return insertedRows;
	}

	/**
	 * update customer information
	 * 
	 * @param customer
	 * @return
	 * @throws DAOException
	 * @throws DuplicateCustomerException
	 */
	public int update(CustomerDetailObject customer) throws DAOException,
			DuplicateCustomerException {
		int updatedRows = 0;
		PreparedStatement pStmt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			CustomerDetailObject det = fetchDetail(customer.getEmailAddress());
			if (det != null && det.getId() != customer.getId()) {
				throw new DuplicateCustomerException(
						"error.database.customer.duplicate", customer
								.getEmailAddress(), null, ErrorLevel.WARNING);
			}
			pStmt = conn.prepareStatement(UPDATE_SQL);
			pStmt.setString(1, customer.getEmailAddress());
			pStmt.setString(2, customer.getFirstName());
			pStmt.setString(3, customer.getLastName());
			pStmt.setString(4, customer.getPreferredCarrier());
			pStmt.setBoolean(5, customer.getReceiveEmail());
			pStmt.setString(6, customer.getAddress().getAddress1());
			pStmt.setString(7, customer.getAddress().getAddress2());
			pStmt.setString(8, customer.getAddress().getCity());
			pStmt.setString(9, customer.getAddress().getState());
			pStmt.setString(10, customer.getAddress().getZip());
			pStmt.setInt(11, customer.getId());

			updatedRows = pStmt.executeUpdate();

		} catch (SQLException se) {
			String[] valueReplacementArray = { customer.getEmailAddress() };
			throw new DAOException(se, "error.database.customer.save",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}
		return updatedRows;
	}

	/**
	 * delete customer
	 * 
	 * @param emailAddress
	 * @throws DAOException
	 */
	public void delete(int id) throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			pSmnt = conn.prepareStatement(DELETE_ID_SQL);
			pSmnt.setInt(1, id);
			pSmnt.executeUpdate();
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { new Integer(id).toString() };
			throw new DAOException(se, "error.database.customer.delete",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}
	}

	/**
	 * Set results to data object: customer
	 */
	protected CustomerDetailObject extractCustomerDetailObjectFromResultSet(
			ResultSet rs) throws SQLException {
		CustomerDetailObject customer = new CustomerDetailObject();
		customer.setId(rs.getInt("ID"));
		customer.setEmailAddress(rs.getString("EMAIL"));
		customer.setFirstName(rs.getString("FIRSTNAME"));
		customer.setLastName(rs.getString("LASTNAME"));
		customer.setPreferredCarrier(rs.getString("PREFERRED_CARRIER"));
		customer.setReceiveEmail(rs.getBoolean("CAN_RECV_MAIL"));

		Address address = new Address();
		address.setAddress1(rs.getString("ADDRESS1"));
		address.setAddress2(rs.getString("ADDRESS2"));
		address.setCity(rs.getString("CITY"));
		address.setState(rs.getString("STATE"));
		address.setZip(rs.getString("ZIPCODE"));

		customer.setAddress(address);

		return customer;
	}

	/**
	 * set data to customer object summary
	 */
	protected CustomerSummaryObject extractCustomerSummaryObjectFromResultSet(
			ResultSet rs) throws SQLException {
		CustomerSummaryObject customer = new CustomerSummaryObject();
		customer.setId(rs.getInt("ID"));
		customer.setEmailAddress(rs.getString("EMAIL"));
		customer.setFirstName(rs.getString("FIRSTNAME"));
		customer.setLastName(rs.getString("LASTNAME"));

		return customer;
	}

	/**
	 * database connection processor
	 */
	protected Connection getConnection() throws DAOException {
		Connection dbConnection = null;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			dbConnection = DriverManager
					.getConnection("jdbc:odbc:helloworld_TRAINING");

		} catch (ClassNotFoundException cfe) {
			throw new DAOException(cfe, "error.database.config", null,
					ErrorLevel.FATAL);
		} catch (SQLException se) {
			throw new DAOException(se, "error.database.config", null,
					ErrorLevel.FATAL);
		}
		return dbConnection;
	}

	/**
	 * @author Tuyen-DM
	 *         <p>
	 *         Mysql connection test
	 *         </p>
	 * @return
	 * @throws DAOException
	 */
	protected Connection getMySQLConnnection() throws DAOException {
		Connection dbConnection = null;
		try {
			// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			// dbConnection =
			// DriverManager.getConnection("jdbc:odbc:helloworld_TRAINING");
			// get database properties test
			Properties prop = new Properties();
			ResourceBundle resource = ResourceBundle.getBundle("db-config");
			Enumeration enumTemp = resource.getKeys();
			while (enumTemp.hasMoreElements()) {
				String key = (String) enumTemp.nextElement();
				prop.put(key, resource.getString(key));
			}	

			String userName = prop.getProperty("username");
			String password = prop.getProperty("userpassword");
			String url = prop.getProperty("database_url");
			Class.forName(prop.getProperty("database_Server"));
			dbConnection = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");

		} catch (ClassNotFoundException cfe) {
			throw new DAOException(cfe, "error.database.config", null,
					ErrorLevel.FATAL);
		} catch (SQLException se) {
			throw new DAOException(se, "error.database.config", null,
					ErrorLevel.FATAL);
		}
		return dbConnection;
	}

	/**
	 * debug test
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			CustomerDAO_AccessImpl dao = new CustomerDAO_AccessImpl();
			CustomerDetailObject cust = dao
					.fetchDetail("antishyness@yahoo.com");
			System.out.println(cust);
		} catch (DAOException de) {
			// TODO: handle exception
			Log log = LogFactory.getLog("CustomerDAO");
			de.log(log);
		}
	}
}
